package fy.form.hr_plans.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import fy.config.FyConfig;
import fy.form.hr_plans.bean.PlanBean;
import fy.form.hr_plans.bean.PsndevelopNeedBean;
import fy.form.hr_plans.bean.PsndevelopTrmBean;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class PlansDao {
	private static Log log = LogFactory.getLog(PlansDao.class);

	private static Connection connect() throws Exception {
		return DBUtil.connect(FyConfig.getEHR_DB_CONFIG());
	}

	/**
	 * 新计划
	 * 
	 * @param planBean
	 */
	public static boolean newPlan(PlanBean planBean) {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {

			conn = connect();
			String pk_psndev = String.valueOf(planBean.getSummaryId());

			pst = conn
					.prepareStatement("select * from psndevelop_h where pk_psndev=?");
			pst.setString(1, pk_psndev);

			rs = pst.executeQuery();

			if (rs.next()) {
				// 如果是撤销后重新发起的，则删除数据
				DBUtil.close(rs, pst);

				pst = conn
						.prepareStatement("delete from psndevelop_trm where pk_psndev=?");
				pst.setString(1, pk_psndev);
				pst.executeUpdate();
				DBUtil.close(pst);

				pst = conn
						.prepareStatement("delete from psndevelop_need where pk_psndev=?");
				pst.setString(1, pk_psndev);
				pst.executeUpdate();
				DBUtil.close(pst);

				pst = conn
						.prepareStatement("delete from psndevelop_h where pk_psndev=?");
				pst.setString(1, pk_psndev);
				pst.executeUpdate();
				DBUtil.close(pst);
			}
			DBUtil.close(rs, pst);

			pst = conn
					.prepareStatement("select tab1.pk_corp, tab1.pk_deptdoc, tab1.pk_om_job, tab1.pk_psndoc, tab1.pk_psnbasdoc from bd_psndoc tab1 where tab1.psnclscope='0' and tab1.psncode=?");

			pst.setString(1, planBean.getMemberCode());
			rs = pst.executeQuery();
			if (rs.next()) {
				String pk_corp = rs.getString(1);
				String pk_deptdoc = rs.getString(2);
				String pk_om_job = rs.getString(3);
				String pk_psndoc = rs.getString(4);
				String pk_psnbasdoc = rs.getString(5);

				DBUtil.close(rs, pst);

				conn.setAutoCommit(false);

				pst = conn
						.prepareStatement("insert into psndevelop_h ( pk_psndev,pk_psnbasdoc,pk_psndoc,psncode,pk_deptdoc,pk_om_job,cbegindate,cenddate,cstatus,competency_gap,develop_need,course_intent,strengths,jobgoal,course_intent_remote,ts,vbillno,pk_corp,sreason) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
				// "insert into psndevelop_h
				// ( pk_psndev,pk_psnbasdoc,pk_psndoc,psncode,
				// pk_deptdoc,pk_om_job,cbegindate,cenddate,cstatus,
				// competency_gap,develop_need,course_intent,
				// strengths,jobgoal,course_intent_remote,
				// ts,vbillno,pk_corp,sreason)
				// values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
				pst.setString(1, pk_psndev);
				pst.setString(2, pk_psnbasdoc);
				pst.setString(3, pk_psndoc);
				pst.setString(4, planBean.getMemberCode());
				pst.setString(5, pk_deptdoc);
				pst.setString(6, pk_om_job);

				pst.setString(7, planBean.getPlanStartDate());
				pst.setString(8, planBean.getPlanEndDate());
				pst.setString(9, "1");
				pst.setString(10, planBean.getCompetency_gap());
				pst.setString(11, MyUtil.getLimitLengthString(
						planBean.getDevelop_need(), 1995, "…"));
				pst.setString(12, planBean.getCourse_intent());
				pst.setString(13, planBean.getStrengths());
				pst.setString(14, planBean.getJobgoal());
				pst.setString(15, planBean.getCourse_intent_remote());
				pst.setString(16, MyUtil.getTodayDateStr());
				pst.setString(17, planBean.getFormNumber());
				pst.setString(18, pk_corp);
				pst.setString(19, planBean.getDepManager());

				pst.addBatch();
				pst.executeBatch();

				DBUtil.close(pst);

				insertAllRepeatTable(planBean, conn, pst, pk_psndev);

				conn.commit();
				success = true;
			}

		} catch (Exception e) {
			DBUtil.rollback(conn);
			log.error("插入数据异常", e);
			e.printStackTrace();
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(rs, pst, conn);
		}

		return success;
	}

	private static void insertPsndevelopNeed(
			PsndevelopNeedBean[] psndevelopNeedBeans, PreparedStatement pst,
			String pk_psndev) throws SQLException {
		// insert into psndevelop_need
		// (pk_psndev,needtype,recordnum,jobmajor,
		// joblevel,jobneed) values (?,?,?,?,?,?)");
		for (int i = 0, length = psndevelopNeedBeans.length; i < length; i++) {
			PsndevelopNeedBean psndevelopNeedBean = psndevelopNeedBeans[i];
			if (psndevelopNeedBean.getJobmajor() != null
					&& !"".equals(psndevelopNeedBean.getJobmajor())) {
				pst.setString(1, pk_psndev);
				pst.setString(2, psndevelopNeedBean.getNeedtype());
				pst.setString(3, psndevelopNeedBean.getRecordnum());
				pst.setString(4, psndevelopNeedBean.getJobmajor());
				pst.setString(5, psndevelopNeedBean.getJoblevel());
				pst.setString(6, psndevelopNeedBean.getJobneed());
				pst.setString(7, psndevelopNeedBean.getJoblevel1());
				pst.addBatch();
			}

		}
	}

	/**
	 * 直接部门主管审批
	 * 
	 * @param planBean
	 */
	public static boolean updatePlan(PlanBean planBean) {
		boolean success = false;

		Connection conn = null;
		PreparedStatement pst = null;

		try {
			conn = connect();
			conn.setAutoCommit(false);
			String pk_psndev = String.valueOf(planBean.getSummaryId());

			pst = conn
					.prepareStatement("update psndevelop_h set cbegindate=?,cenddate=?,cstatus=?,competency_gap=?,develop_need=?,course_intent=?,strengths=?,jobgoal=?,course_intent_remote=?,ts=?,sreason=? where pk_psndev=?");
			// update psndevelop_h set
			// cbegindate=?,cenddate=?,cstatus=?,competency_gap=?,
			// develop_need=?,course_intent=?,strengths=?,jobgoal=?,
			// course_intent_remote=?,ts=?,sreason=? where pk_psndev=?");
			pst.setString(1, planBean.getPlanStartDate());
			pst.setString(2, planBean.getPlanEndDate());
			pst.setString(3, "2");
			pst.setString(4, planBean.getCompetency_gap());
			pst.setString(5, planBean.getDevelop_need());
			pst.setString(6, planBean.getCourse_intent());
			pst.setString(7, planBean.getStrengths());
			pst.setString(8, planBean.getJobgoal());
			pst.setString(9, planBean.getCourse_intent_remote());
			pst.setString(10, MyUtil.getTodayDateStr());
			pst.setString(11, planBean.getDepManager());
			pst.setString(12, pk_psndev);

			pst.addBatch();
			pst.executeBatch();
			DBUtil.close(pst);

			// 删除已存在的学习课程安排
			pst = conn
					.prepareStatement("delete from psndevelop_trm where pk_psndev=?");
			pst.setString(1, pk_psndev);
			pst.executeUpdate();
			DBUtil.close(pst);

			// 删除已存在的能力评估
			pst = conn
					.prepareStatement("delete from psndevelop_need where pk_psndev=?");
			pst.setString(1, pk_psndev);

			pst.execute();
			DBUtil.close(pst);

			insertAllRepeatTable(planBean, conn, pst, pk_psndev);

			conn.commit();
			success = true;
		} catch (Exception e) {
			DBUtil.rollback(conn);
			log.error("《个人发展计划》更新数据异常", e);
			e.printStackTrace();
		} finally {
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}

		return success;
	}

	private static void insertAllRepeatTable(PlanBean planBean,
			Connection conn, PreparedStatement pst, String pk_psndev)
			throws Exception {
		// 插入还未存在的学习安排的列
		pst = conn
				.prepareStatement("insert into psndevelop_trm (pk_psndev,recordnum,course_name,teach_mothed,course_content,course_time) values (?,?,?,?,?,?)");
		for (int i = 0, length = planBean.getPsndevelopTrmBeans().length; i < length; i++) {
			PsndevelopTrmBean psndevelopTrmBean = planBean
					.getPsndevelopTrmBeans()[i];
			if (psndevelopTrmBean.getCourse_name() != null
					&& !"".equals(psndevelopTrmBean.getCourse_name())
					|| psndevelopTrmBean.getCourse_content() != null
					&& !"".equals(psndevelopTrmBean.getCourse_content())) {

				pst.setString(1, pk_psndev);
				pst.setString(2, psndevelopTrmBean.getRecordnum());
				pst.setString(3, psndevelopTrmBean.getCourse_name());
				pst.setString(4, psndevelopTrmBean.getTeach_mothed());
				pst.setString(5, psndevelopTrmBean.getCourse_content());
				pst.setString(6, psndevelopTrmBean.getCourse_time());
				pst.addBatch();
			}
		}
		pst.executeBatch();
		DBUtil.close(pst);

		pst = conn
				.prepareStatement("insert into psndevelop_need (pk_psndev,needtype,recordnum,jobmajor,joblevel,jobneed,joblevel2) values (?,?,?,?,?,?,?)");
		insertPsndevelopNeed(planBean.getProfessionalCompetenceNeedBeans(),
				pst, pk_psndev);
		insertPsndevelopNeed(planBean.getSenseOfWorthNeedBeans(), pst,
				pk_psndev);
		pst.executeBatch();
		DBUtil.close(pst);
	}

	/**
	 * 被撤销或者终止的计划
	 * 
	 * @param summaryId
	 */
	public static boolean invalidPlan(long summaryId) {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = connect();
			pst = conn
					.prepareStatement("update psndevelop_h set cstatus = '0' where pk_psndev=?");

			pst.setString(1, String.valueOf(summaryId));

			if (1 == pst.executeUpdate()) {
				success = true;
			} else {
				log.error("《个人发展计划》未查找到,summaryId:" + summaryId);
			}

		} catch (Exception e) {
			log.error("《个人发展计划》更新状态为作废异常", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return success;
	}

	/**
	 * 被取回或者被回退的计划
	 * 
	 * @param summaryId
	 */
	public static boolean flowBackPlan(long summaryId) {
		boolean success = false;

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = connect();

			pst = conn
					.prepareStatement("select cstatus from  psndevelop_h where pk_psndev=?");
			pst.setString(1, String.valueOf(summaryId));
			rs = pst.executeQuery();

			if (rs.next()) {

				int state = Integer.valueOf(rs.getString(1));
				DBUtil.close(rs, pst);

				if (state > 0) {

					pst = conn
							.prepareStatement("update psndevelop_h set cstatus = ? where pk_psndev=?");

					pst.setString(1, String.valueOf(state - 1));
					pst.setString(2, String.valueOf(summaryId));

					if (1 == pst.executeUpdate()) {
						success = true;
					} else {
						log.error("《个人发展计划》未查找到,summaryId:" + summaryId);
					}
				}
			} else {
				log.error("《个人发展计划》未查找到,summaryId:" + summaryId);
			}
		} catch (Exception e) {
			log.error("《个人发展计划》被取回，更新状态为等待部门主管审批异常", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pst, conn);
		}

		return success;
	}

	/**
	 * 审批结束的计划
	 * 
	 * @param summaryId
	 */
	public static boolean finishPlan(long summaryId) {
		boolean success = false;

		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = connect();
			pst = conn
					.prepareStatement("update psndevelop_h set cstatus = '3' where pk_psndev=?");
			pst.setString(1, String.valueOf(summaryId));

			if (1 == pst.executeUpdate()) {
				success = true;
			} else {
				log.error("《个人发展计划》未查找到,summaryId:" + summaryId);
			}
		} catch (Exception e) {
			log.error("《个人发展计划》更新状态为已结束异常", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(pst);
			DBUtil.close(conn);
		}

		return success;
	}
}
